package com.dao.hm;


import java.math.BigDecimal;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.entity.Chanceinfo;

public interface ChanceInfoDAO extends CrudRepository<Chanceinfo, Long>, JpaSpecificationExecutor<Chanceinfo>{
	@Query("from Chanceinfo c where c.logic=1")
	public Page<Chanceinfo> queryNoCriteria(Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and c.theme like ?1")
	public Page<Chanceinfo> queryByTheme(String theme, Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and c.stage=?1")
	public Page<Chanceinfo> queryFastByStage(String stage, Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and c.status=?1")
	public Page<Chanceinfo> queryFastByStatus(String status, Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and c.type=?1")
	public Page<Chanceinfo> queryFastByType(String type, Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and c.first=?1")
	public Page<Chanceinfo> queryFastByFirst(String first, Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and c.stars=?1")
	public Page<Chanceinfo> queryFastByStars(BigDecimal stars, Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and 0<=c.stagestop and c.stagestop<=7")
	public Page<Chanceinfo> queryFastByStagestop1( Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and 7<c.stagestop and c.stagestop<=15")
	public Page<Chanceinfo> queryFastByStagestop2(Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and 15<c.stagestop and c.stagestop<=30")
	public Page<Chanceinfo> queryFastByStagestop3( Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and 30<c.stagestop and c.stagestop<=60")
	public Page<Chanceinfo> queryFastByStagestop4( Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and 60<c.stagestop and c.stagestop<=90")
	public Page<Chanceinfo> queryFastByStagestop5( Pageable p);

	@Query("from Chanceinfo c where c.logic=1 and c.stagestop>90")
	public Page<Chanceinfo> queryFastByStagestop6( Pageable p);
	
	@Query("select COUNT(c.user.userId),c.user.userId from Chanceinfo c where c.logic=1 GROUP BY c.user.userId")
	public List<Object[]> queryByUserid();
	
	@Query("select COUNT(c.from),c.from from Chanceinfo c where c.logic=1 GROUP BY c.from")
	public List<Object[]> queryByFrom();
	
	@Query("select COUNT(c.type),c.type from Chanceinfo c where c.logic=1 GROUP BY c.type")
	public List<Object[]> queryByType();
	
	@Query("select COUNT(*),to_char(c.findtime,'yyyy-mm') from Chanceinfo c where c.logic=1 GROUP BY to_char(c.findtime,'yyyy-mm') order by to_char(c.findtime,'yyyy-mm') ASC")
	public List<Object[]> queryByFindtime();
	
	@Query("select COUNT(*),to_char(c.expecttime,'yyyy-mm') from Chanceinfo c where c.logic=1 and c.expecttime is not null GROUP BY to_char(c.expecttime,'yyyy-mm') order by to_char(c.expecttime,'yyyy-mm') ASC")
	public List<Object[]> queryByExpecttime();
	
	@Query("select COUNT(*),to_char(c.birth,'yyyy-mm') from Chanceinfo c where c.logic=1 GROUP BY to_char(c.birth,'yyyy-mm') order by to_char(c.birth,'yyyy-mm') ASC")
	public List<Object[]> queryByBirth();
	
	
	
	
	
	@Query("select COUNT(*),c.stage from Chanceinfo c where c.logic=1 and c.status='跟踪' GROUP BY c.stage order by COUNT(*) ASC")
	public List<Object[]> queryStageNum();
	
	@Query("select SUM(c.expectmoney),c.stage from Chanceinfo c where c.logic=1 and c.status='跟踪' GROUP BY c.stage order by SUM(c.expectmoney) ASC")
	public List<Object[]> queryStageExpectmoney();
}
